package intranet.models;
import java.sql.*;
public class CourseModel {
	Connection DBConnection=null;
	public CourseModel(){
		DatabaseModel DBModel=new DatabaseModel();
		DBConnection=DBModel.GetConnection();
	}
	public ResultSet SelectCourseFileByType(int Course_ID,String Description){
		ResultSet result=null;
		try{
			Statement ConStatement=DBConnection.createStatement();
			String Sql="SELECT F.FILE_ID,F.COURSE_ID,F.FILE_TITLE,F.FILE_DESC,F.FILE_NAME,";
			Sql+="F.FILE_PATH,F.THUMBNAIL_FILE, F.THUMBNAIL_PATH,F.FILE_SIZE,F.PREPARED_BY FROM COURSE_FILES F INNER JOIN ";
			Sql+="COURSE_FILE_TYPES T ON F.TYPE_ID=T.TYPE_ID WHERE T.TYPE_DESC='"+ Description +"' AND F.COURSE_ID="+Course_ID;
			result=ConStatement.executeQuery(Sql);
		}catch(Exception e){
			e.getLocalizedMessage();
		}
		return result;
	}		
	public ResultSet SelectInstructionFile(int Course_ID){
		ResultSet result=null;
		try{
			Statement ConStatement=DBConnection.createStatement();
			String Sql=" SELECT * FROM INSTRUCTION_FILES WHERE COURSE_ID="+Course_ID;
			result=ConStatement.executeQuery(Sql);
		}catch(Exception e){
			e.getLocalizedMessage();
		}
		return result;
	}	
	public ResultSet SelectCourse(int Course_ID){
		ResultSet result=null;
		try{
			Statement ConStatement=DBConnection.createStatement();
			String Sql=" SELECT * FROM COURSES WHERE COURSE_ID="+Course_ID;
			result=ConStatement.executeQuery(Sql);
		}catch(Exception e){
			e.getLocalizedMessage();
		}
		return result;
	}
	public ResultSet SelectTopCourse(){
		ResultSet result=null;
		try{
			Statement ConStatement=DBConnection.createStatement();
			String Sql="SELECT COURSE_ID,COURSE_NAME,COURSE_DESC,VIEW_NUMBER,TO_CHAR(DATE_POST,'DD/MM/YYYY') AS DATE_POST,USER_POST FROM COURSES ORDER BY VIEW_NUMBER DESC";
			result=ConStatement.executeQuery(Sql);
		}catch(Exception e){
			e.getLocalizedMessage();
		}
		return result;
	}	
	
	public ResultSet CourseFile(int File_ID){
		ResultSet result=null;
		try{
			Statement ConStatement=DBConnection.createStatement();
			String Sql="SELECT * FROM COURSE_FILES WHERE FILE_ID="+File_ID;
			result=ConStatement.executeQuery(Sql);
		}catch(Exception e){
			e.getLocalizedMessage();
		}
		return result;
	}	
	
	public int RowCount(ResultSet rs) throws SQLException{
		int result=0;
		while(rs.next()){
			result++;
		}
		return result;
	}
	public ResultSet SelectOtherCourse() throws SQLException{
		ResultSet result=null;
		int i=0;
		int RowCount=this.RowCount(this.SelectTopCourse());
		int EndIndex=0;
		String Condition=(RowCount>0)?" WHERE ":"";
		/* Generate CONDITIONAL STRING TO AVOID SELECTING THE SAME COURSE AS TOP COURSE */
		EndIndex=(RowCount>=5)?4:RowCount-1;
		ResultSet TopCourse=this.SelectTopCourse();
		while(TopCourse.next()){
			if(i<EndIndex){
				Condition+="COURSE_ID!="+TopCourse.getInt("COURSE_ID")+" AND ";
			}
			if(i==EndIndex){
				Condition+="COURSE_ID!="+TopCourse.getInt("COURSE_ID");
			}
			i++;
		}

		try{
			Statement ConStatement=DBConnection.createStatement();
			String Sql="SELECT COURSE_ID,COURSE_NAME,COURSE_DESC,VIEW_NUMBER,TO_CHAR(DATE_POST,'DD/MM/YYYY') AS DATE_POST,USER_POST FROM COURSES "+Condition+" ORDER BY DATE_POST DESC";
			result=ConStatement.executeQuery(Sql);
		}catch(Exception e){
			e.getLocalizedMessage();
		}
		return result;
	}	
	
	public ResultSet SelectMoreCourse() throws SQLException{
		ResultSet result=null;
		try{
			Statement ConStatement=DBConnection.createStatement();
			String Sql="SELECT COURSE_ID,COURSE_NAME,COURSE_DESC,VIEW_NUMBER,TO_CHAR(DATE_POST,'DD/MM/YYYY') AS DATE_POST,USER_POST FROM COURSES ORDER BY DATE_POST DESC";
			result=ConStatement.executeQuery(Sql);
		}catch(Exception e){
			e.getLocalizedMessage();
		}
		return result;
	}
	public ResultSet SearchCourse(int cat_id,String keywords){
		ResultSet result=null;
		String Sql="";
		if(cat_id==0){		
			if(keywords!=""){
				Sql="SELECT C.COURSE_ID,C.COURSE_NAME,C.COURSE_DESC,C.VIEW_NUMBER,TO_CHAR(C.DATE_POST,'DD/MM/YYYY') AS DATE_POST,C.USER_POST FROM COURSES C LEFT JOIN ";
				Sql+="COURSE_KEYWORDS K ON C.COURSE_ID=K.COURSE_ID WHERE lower(K.KEYWORD) LIKE'%"+keywords.toLowerCase()+"%' OR lower(C.COURSE_NAME) LIKE'%"+keywords.toLowerCase()+"%' ORDER BY C.VIEW_NUMBER DESC";
			}else{
				Sql="SELECT C.COURSE_ID,C.COURSE_NAME,C.COURSE_DESC,C.VIEW_NUMBER,TO_CHAR(C.DATE_POST,'DD/MM/YYYY') AS DATE_POST,C.USER_POST FROM COURSES C ";
				Sql+="ORDER BY C.VIEW_NUMBER DESC";
			}	
			
		}else{
			if(keywords!=""){
				Sql="SELECT C.COURSE_ID,C.COURSE_NAME,C.COURSE_DESC,C.VIEW_NUMBER,TO_CHAR(C.DATE_POST,'DD/MM/YYYY') AS DATE_POST,C.USER_POST FROM COURSES C LEFT JOIN ";
				Sql+="COURSE_KEYWORDS K ON C.COURSE_ID=K.COURSE_ID WHERE (lower(K.KEYWORD) LIKE'%"+keywords.toLowerCase()+"%' OR lower(C.COURSE_NAME) LIKE'%"+keywords.toLowerCase()+"%') AND C.CATEGORY_ID="+cat_id+" ORDER BY C.VIEW_NUMBER DESC";
			}else{
				Sql="SELECT C.COURSE_ID,C.COURSE_NAME,C.COURSE_DESC,C.VIEW_NUMBER,TO_CHAR(C.DATE_POST,'DD/MM/YYYY') AS DATE_POST,C.USER_POST FROM COURSES C ";
				Sql+="WHERE C.CATEGORY_ID="+cat_id+" ORDER BY C.VIEW_NUMBER DESC";
			}	
		}
		try{
			Statement ConStatement=DBConnection.createStatement();
			result=ConStatement.executeQuery(Sql);
		}catch(Exception e){
			e.getLocalizedMessage();
		}
		return result;
	}	
}
